
package DAO;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.*;
import DTO.Order;
import DTO.Item;
import Exception.*;

/**
 * This class represents abstraction for data access from order table in database.
 * This class is called from DAO as a proxy.
 * @see DAO
 * @Date Mar 30, 2011
 * @author Tony Lei
 */
public class OrderDAO {

    private Connection conn;
    private static final Logger logger = Logger.getLogger(OrderDAO.class.getName());

     /**
     * constructor for this class.
     * @param conn connection object
     */
    public OrderDAO(Connection conn) {
        this.conn = conn;
    }

    /**
     * This method inserts a new order into database
     * @param order a data transfer object
     * @return an integer
     * @throws {@link KwickKioskDBException}
     */
    public int insertStoreOrder(Order order) throws KwickKioskDBException {
        int orderID = -1;
        String sqlText = "INSERT INTO orders "
                + "(orderDate, itemsSubTotal, taxAmount, totalItemDiscount, "
                + "orderDiscount, storeCardDiscountTotal, storeName) VALUES ("
                + "curdate(), "
                + "'" + order.getItemSubTotal() + "', "
                + "'" + order.getTaxAmount() + "', "
                + "'" + order.getTotalItemDiscount() + "', "
                + "'" + order.getOrderDiscount() + "', "
                + "'" + order.getStoreCardDiscountTotal() + "', "
                + "'" + order.getStoreName() + "')";
        try {
            if (updateDB(sqlText)) {
                sqlText = "select max(o.oid) from orders o";
                Statement sqlStatement = conn.createStatement();
                ResultSet rs = sqlStatement.executeQuery(sqlText);
                if (rs.next()) {
                    orderID = rs.getInt(1);
                }
                rs.close();
                sqlStatement.close();
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "OrderDAO : insertStoreOrder" + ex);
            try {
                conn.rollback();
            } catch (SQLException ex2) {
                System.out.println(ex2.toString());
            }
        }
        return orderID;
    }

    /**
     * This method inserts a new order made from web
     * @param order  a data transfer object
     * @return an integer for order id 
     * @throws {@link KwickKioskDBException}
     */
    public int insertDesktopOrder(Order order) throws KwickKioskDBException {
        int orderID = -1;
        String sqlText = "INSERT INTO orders "
                + "(orderDate, itemsSubTotal, taxAmount, shippingFee, "
                + "totalItemDiscount, orderDiscount, storeCardDiscountTotal, "
                + "storeName, shid, csid, aid) VALUES ("
                + "curdate(), "
                + "'" + order.getItemSubTotal() + "', "
                + "'" + order.getTaxAmount() + "', "
                + "'" + order.getShippingFee() + "', "
                + "'" + order.getTotalItemDiscount() + "', "
                + "'" + order.getOrderDiscount() + "', "
                + "'" + order.getStoreCardDiscountTotal() + "', "
                + "'" + order.getStoreName() + "', "
                + order.getShID() + ", "
                + order.getStoreCardNumber() + ", "
                + order.getAddressID()
                + ")";
        try {
            if (updateDB(sqlText)) {
                sqlText = "select max(o.oid) from orders o";
                Statement sqlStatement = conn.createStatement();
                ResultSet rs = sqlStatement.executeQuery(sqlText);
                if (rs.next()) {
                    orderID = rs.getInt(1);
                }
                rs.close();
                sqlStatement.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            logger.log(Level.SEVERE, null, "OrderDAO : inserDesktopOrder" + ex);
            try {
                conn.rollback();
            } catch (SQLException ex2) {
                System.out.println(ex2.toString());
            }
        }
        return orderID;
    }

    /**
     * This method updates the status of an order
     * @param id an integer for order id.
     * @param status a string for status of an order.
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean updateOrderStatus(int id, String status)
            throws KwickKioskDBException {
        String sqlText = "UPDATE orders SET "
                + "orderStatus='" + status + "' "
                + "WHERE oid=" + id;
        return updateDB(sqlText);
    }

    /**
     * This method updates quantity of items in store
     * @param order a data transfer object
     * @throws {@link KwickKioskDBException}
     */
    public void updateItemQtyInStore(Order order) throws KwickKioskDBException {
        Statement sqlStatement;
        List<Item> list = order.getItemList();
        System.out.println("quantity " + list.get(0).getNewQtyInStore());
        try {
            sqlStatement = conn.createStatement();
            for (int i = 0; i != list.size(); i++) {
                String sqlText = "UPDATE iteminstore SET "
                        + "qty=" + list.get(i).getNewQtyInStore()
                        + " WHERE storeName='" + order.getStoreName()
                        + "' and upc='" + list.get(i).getUpc() + "'";
                sqlStatement.executeUpdate(sqlText);
            }
            sqlStatement.close();
            conn.commit();
        } catch (SQLException ex) {
            try {
                conn.rollback();
                logger.log(Level.SEVERE, null, "OrderDAO : updateQtyInStore"
                        + ex);
                throw new KwickKioskDBException("OrderDAO : updateQtyInStore",
                        ex.getMessage());
            } catch (SQLException ex2) {
                logger.log(Level.SEVERE, null, "OrderDAO : updateQtyInStore"
                        + ex2);
                throw new KwickKioskDBException("OrderDAO : updateQtyInStore",
                        ex2.getMessage());
            }
        }
    }

   /**
    * This method inserts ordered items into database and its is invoked as part of order transaction
    * @param order a data transfer object
    * @param oid an integer 
    * @throws {@link KwickKioskDBException}
    */
    public void insertItems(Order order, int oid) throws KwickKioskDBException {
        Statement sqlStatement;
        List<Item> list = order.getItemList();
        try {
            sqlStatement = conn.createStatement();
            for (int i = 0; i != list.size(); i++) {
                String sqlText = "INSERT INTO inorder "
                        + "(upc, oid, qty, soldPrice, discountAmount, storeCardDiscount) "
                        + "VALUES ("
                        + "'" + list.get(i).getUpc() + "', "
                        + oid + ", "
                        + list.get(i).getQty() + ", "
                        + list.get(i).getSoldPrice() + ", "
                        + list.get(i).getDiscountAmount() + ", "
                        + list.get(i).getStoreCardDiscount() + ")";
                sqlStatement.executeUpdate(sqlText);
            }
            sqlStatement.close();
            conn.commit();
        } catch (SQLException ex) {
            try {
                conn.rollback();
                logger.log(Level.SEVERE, null, "OrderDAO : insertItems" + ex);
                throw new KwickKioskDBException("OrderDAO : insertItems",
                        ex.getMessage());
            } catch (SQLException ex2) {
                logger.log(Level.SEVERE, null, "OrderDAO : insertItems" + ex2);
                throw new KwickKioskDBException("OrderDAO : insertItems",
                        ex2.getMessage());
            }
        }
    }


    /**
     * This method searches orders by date
     * @param date string for date as search criteria
     * @return  a list of order objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Order> searchByDate(String date)
            throws KwickKioskDBException {
        // End date string format: yyyy-mm-dd
        ArrayList<Order> list = new ArrayList<Order>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from orders o "
                    + "where o.orderDate='" + date + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Order(rs.getInt(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getDouble(6),
                        rs.getDouble(7),
                        rs.getDouble(8),
                        rs.getDouble(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getInt(12),
                        rs.getInt(13)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "OrderDAO : searchDate" + ex);
            throw new KwickKioskDBException("OrderDAO : searchDate",
                        ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches orders by order id
     * @param i an integer order id
     * @return an order data transfer object
     * @throws {@link KwickKioskDBException}
     */
    public Order searchByNum(int i)
            throws KwickKioskDBException {
        // End date string format: yyyy-mm-dd
        Order result = null;
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from orders o "
                    + "where o.oid=" + i;
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                result = new Order(rs.getInt(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getDouble(6),
                        rs.getDouble(7),
                        rs.getDouble(8),
                        rs.getDouble(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getInt(12),
                        rs.getInt(13));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "OrderDAO : searchByNum" + ex);
            throw new KwickKioskDBException("OrderDAO : searchByNum",
                        ex.getMessage());
        }
        return result;
    }

    /**
     * This method searches orders by order status.
     * @param status a string for status of an order
     * @return  a list of order objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Order> searchByStatus(String status)
            throws KwickKioskDBException {
        // End date string format: yyyy-mm-dd
        ArrayList<Order> list = new ArrayList<Order>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from orders o "
                    + "where o.orderStatus='" + status + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Order(rs.getInt(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getDouble(6),
                        rs.getDouble(7),
                        rs.getDouble(8),
                        rs.getDouble(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getInt(12),
                        rs.getInt(13)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "OrderDAO : searchByStatus" + ex);
            throw new KwickKioskDBException("OrderDAO : searchByStatus",
                        ex.getMessage());
        }
        return list;
    }

    /**
     * This method retrieves all orders from database.
     * @return a list of order objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Order> getAllOrder()
            throws KwickKioskDBException {
        ArrayList<Order> list = new ArrayList<Order>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from orders";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Order(rs.getInt(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getDouble(6),
                        rs.getDouble(7),
                        rs.getDouble(8),
                        rs.getDouble(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getInt(12),
                        rs.getInt(13)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "OrderDAO : getAllOrder" + ex);
            throw new KwickKioskDBException("OrderDAO : getAllOrder",
                        ex.getMessage());
        }
        return list;
    }

    /**
     * This method retrieves orders placed by a customer
     * @param csid an integer for customer id
     * @return  a list of order objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Order> getCustomerOrder(int csid)
            throws KwickKioskDBException {
        ArrayList<Order> list = new ArrayList<Order>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from orders o "
                    + "where o.csid=" + csid;
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Order(rs.getInt(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getDouble(6),
                        rs.getDouble(7),
                        rs.getDouble(8),
                        rs.getDouble(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getInt(12),
                        rs.getInt(13)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "OrderDAO : getAllOrder" + ex);
            throw new KwickKioskDBException("OrderDAO : getAllOrder",
                        ex.getMessage());
        }
        return list;
    }

     /**
     * this method updates the database
     * @param sqlText a string for data manipulation  query
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    private boolean updateDB(String sqlText) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            int value = sqlStatement.executeUpdate(sqlText);
            sqlStatement.close();
            if (value == 1) {
                conn.commit();
                return true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            try {
                conn.rollback();
                logger.log(Level.SEVERE, null, "OrderDAO : updateDB" + ex);
                throw new KwickKioskDBException("OrderDAO : updateDB",
                        ex.getMessage());
            } catch (SQLException ex2) {
                logger.log(Level.SEVERE, null, "OrderDAO : updateDB" + ex2);
                throw new KwickKioskDBException("OrderDAO : updateDB",
                        ex2.getMessage());
            }
        }

        return false;
    }

    /**
     * This method retrieves item information associated with an order
     * @param oid an integer for order id.
     * @return  a list of items.
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> getItemByOrder(int oid)
            throws KwickKioskDBException {

        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select i.upc, i.itemName, i.make, i.model, "
              + "io.qty, io.soldPrice "
              + "from item i, inorder io, orders o "
              + "where i.upc=io.upc and io.oid=o.oid and o.oid=" + oid ;
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                                  rs.getString(2),
                                  rs.getString(3),
                                  rs.getString(4),
                                  rs.getInt(5),
                                  rs.getDouble(6)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ItemDAO : searchWithKeyWord" + ex);
            throw new KwickKioskDBException("ItemDAO : searchWithKeyWord",
                        ex.getMessage());
        }
        return list;
    }

}
